"""
对没用趋势数据的虚拟机进行记录，虚拟机恢复后通过该方法来校验恢复情况。
"""
import pymysql.cursors
from mytool.office.excel import SingleExcel

c = pymysql.connect(host='172.16.6.92',
                    user='yjs',
                    password='*',
                    db='zabbix',
                    charset='utf8mb4',
                    cursorclass=pymysql.cursors.DictCursor) # DictCursor 返回的是一个字典对象。
output_path = 'f:\\out.txt'


class SQL:
    def __init__(self):
        self.hosts_data = {'data': {}}
        pass

    def query(self, sql):
        _hostid = []
        with c.cursor() as cc:
            rows = cc.execute(sql)
            for line in range(rows):
                data = cc.fetchone()
                _hostid.append(data)
        return _hostid

    def find_last_trends(self, item_key, trends_table):
        host_sql = 'select i.hostid, h.name, ii.ip, i.itemid from items i ' \
                   'join hosts h on h.hostid = i.hostid ' \
                   'join interface ii on ii.hostid = h.hostid ' \
                   'where i.key_ = "{}" and h.status = 0'.format(item_key)
        history_sql = 'select * from {} where itemid = {} order by clock desc limit 1'
        # 返回一个数组，数组中包含n个字典数据包含虚拟机数据，数据包含：hostid，name，ip，itemid。
        hosts = self.query(host_sql)

        for host in hosts:
            # 将所有虚拟机保存进
            trend_data = self.query(history_sql.format(trends_table, host['itemid']))
            if trend_data:
                for trend in trend_data:
                    if trend['clock'] > 1587938400:
                        if 'available' in item_key:
                            value_max = int(100 - trend['value_max'])
                            self.hosts_data['data'].setdefault(host['name'], [host['ip'], value_max])
                        elif 'used' in item_key:
                            value_max = int(trend['value_max'])
                            self.hosts_data['data'].setdefault(host['name'], [host['ip'], value_max])


class TrendExcel(SingleExcel):
    """
    对趋势虚拟机进行处理，获取某个趋势所有虚拟机、有趋势数据虚拟机、无趋势虚拟机。
    """
    def __init__(self, read_path=None, set_read_sheet=None, write_path=None, set_write_sheet=None, read_column=None):
        super().__init__(read_path=read_path, set_read_sheet=set_read_sheet, write_path=write_path, set_write_sheet=set_write_sheet, read_column=read_column)

    def read_all_sheet(self, _read_column):
        """
        将excel表中一列作为字典中的key，其他列作为对应列的值（用列表存放）。
        :param _read_column: 读取的excel某几列。
        :return: 字典。
        """
        sheets = {}
        for row in range(1, 9999):
            row_sheets = []
            for column in _read_column:
                r_c = column + str(row)
                row_sheets.append(self.reader[r_c].value)
            itemid = row_sheets[0]
            sheets[itemid] = row_sheets[1:]
            if row_sheets:
                sheets.setdefault(itemid, row_sheets)
            else:
                break
        return sheets

    def write_all_sheet(self, sheets: dict, column: str):
        """
        对几个字典内容保存到一个工作簿中。
        :param sheets:多个字典。
        :param column:一个字典保存对应哪几列。
        """
        row = 0
        print(sheets)
        for sheet in sheets.keys():
            if sheet:
                row += 1
                for n, col in enumerate(column):
                    c_r = col + str(row)
                    if n == 0:
                        self.writer[c_r] = sheet
                    else:
                        print(c_r, len(sheets), sheet, n)
                        self.writer[c_r] = sheets[sheet][n - 1]
        self.wb.save(filename=self.write_path)


def find_trends():
    """
    从数据库中检测没有数据库的虚拟机，并将其写入excel。
    """
    item_to_table = {
        # 'system.localtime': 'trends_uint',
        # 'system.uptime': 'trends_uint',
        'vm.memory.size[pavailable]': 'trends',
        'vm.memory.size[pused]': 'trends',
        # 'system.cpu.load[all,avg1]': 'trends',
        # 'sockstat.sockets': 'trends_uint',
        # 'vfs.fs.size[/,total]': 'trends_uint',
        # 'system.swap.size[,pused]': 'trends'
    }
    data = []
    for key in item_to_table.keys():
        sql = SQL()
        data.append(sql)
        sql.find_last_trends(key, item_to_table[key])
    temp = {}
    for _data in data:
        temp.update(_data.hosts_data['data'])

    write_path = 'f:\\nodata\\trends\\4-27(memory).xlsx'
    excel = TrendExcel(write_path=write_path, set_write_sheet='memory')
    excel.write_all_sheet(temp, 'abc')


class IPExcel(SingleExcel):
    """
    对IP表导出的数据进行转换，主要是转换IP地址，将十进制表示的IP地址转变成真实的IP地址格式。
    """
    def __init__(self, read_path=None, set_read_sheet=None, write_path=None, set_write_sheet=None, read_column=None):
        super().__init__(read_path=read_path, set_read_sheet=set_read_sheet, write_path=write_path, set_write_sheet=set_write_sheet, read_column=read_column)

    def int_to_ip(self, num):
        """
        对十进制数转换成IP地址。
        :param num: 十进制。
        :return: IP地址。
        """
        ip = ''
        for i in (24, 16, 8):
            ip += str(num >> i & 0xff)
            ip += '.'
        ip += str(num & 0xff)
        return ip

    def write_all_sheet(self, sheets: dict, column: str):
        for key in sheets:
            key_value = sheets[key]
            if 'a' in key:
                self.writer[key] = self.int_to_ip(key_value)
            else:
                self.writer[key] = key_value
        self.wb.save(filename=self.write_path)


def convert_ip():
    """
    对IP表数据转换成IP地址。
    :return:
    """
    read_path = 'f:\\nodata\\ip_pwd.xlsx'
    ipexcel = IPExcel(read_path=read_path, set_read_sheet='ip_pwd',
                      write_path=read_path, set_write_sheet='ip', read_column='abc')
    ipexcel.write_all_sheet(ipexcel.read_mem_sheet, 'abc')


def user_pwd_merge(d: dict):
    """
    讲用户和密码进行合并。
    :param d:
    :return:
    """
    for key in d:
        if key is not None:
            d[key] = d[key][0] + '/' + d[key][1]


if __name__ == '__main__':
    find_trends()